const { Service } = require('egg')
const Response = require('../utils/Response')

class mymodelService extends Service {
  // 我的学生 ----  分页数据
  async queryStudents(params) {
    const { ctx } = this
    const { mysql } = this.app
    const offSet = params.page === '1' ? 0 : (Number(params.page) - 1) * Number(params.pageSize)
    const pageSize = Number(params.pageSize)
    const stu_name = params?.stu_name ? params.stu_name : null
    const cur_code = params?.cur_code ? params.cur_code : null
    const sqlTotal = `SELECT COUNT(*) as cnt
    FROM orders JOIN students ON orders.stu_code = students.stu_code
    WHERE orders.tea_code=?
      AND (cur_code = ? OR ? IS NULL)
      AND (students.stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL)
      AND order_statusCode <> 4
    ORDER BY order_id DESC`
    // order_statusCode为4是已经退款的
    const sql = `SELECT orders.cur_name, students.stu_code, students.stu_age, students.stu_name, students.stu_gender, students.tel, students.parentTel
    FROM orders JOIN students ON orders.stu_code = students.stu_code
    WHERE orders.tea_code=?
      AND (cur_code = ? OR ? IS NULL)
      AND (students.stu_name LIKE CONCAT('%', ?, '%') OR ? IS NULL)
      AND order_statusCode <> 4
    ORDER BY order_id DESC
    LIMIT ? OFFSET ?`
    try {
      const total = await mysql.query(sqlTotal, [params.tea_code, cur_code, cur_code, stu_name, stu_name])
      const result = await mysql.query(sql, [params.tea_code, cur_code, cur_code, stu_name, stu_name, pageSize, offSet])
      ctx.body = new Response('查询成功', 200, { total: total[0].cnt, result })
    } catch (err) {
      throw new Response('异常错误', 500)
    }
  }

  // 我的模块 ---- 我的课程下拉
  async myScourse(params) {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `SELECT cur_name, cur_code
      FROM time_table JOIN curriculum ON time_table.course_id = curriculum.cur_code
      WHERE time_table.teacher_id = ?`
    try {
      const result = await mysql.query(sql, [2])
      // 使用 Array.filter() 和 Array.findIndex() 进行数组去重
      const uniqueData = result.filter((obj, index, array) => {
        return index === array.findIndex((item) => item.cur_code === obj.cur_code)
      })
      ctx.body = new Response('查询成功', 200, uniqueData)
    } catch (err) {
      throw new Response('异常错误', 500)
    }
  }

  // 我的课表 --- 课程下拉
  async myCourseList(params) {
    const { ctx } = this
    const { mysql } = this.app
    const sql = `SELECT cur.cur_code, cur.cur_name
      FROM time_table as time JOIN curriculum as cur ON time.course_id = cur.cur_code
      WHERE time.teacher_id=?`
    const result = await mysql.query(sql, [params.tea_code])
    ctx.body = new Response('success', 200, result)
  }
}
module.exports = mymodelService
